package JDBC_Test.Fruit.Dao;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;

import JDBC_Test.Fruit.JDBCUtil;
import JDBC_Test.Fruit.Model.SaleRecordModel;

public class FruitRecordDao {

	Connection con = null;
	Statement st = null;
	PreparedStatement ps = null;

	/**
	 * 记录销售业务
	 * 
	 * @param saleRecordModel
	 * @throws SQLException
	 */
	public void insert(SaleRecordModel saleRecordModel) throws SQLException {
		// TODO Auto-generated method stub
		String sql = "insert into record (fruit_name,weight,in_price,out_price,discount,sex,date) values(?,?,?,?,?,?,?)";
		try {
//			System.out.println(saleRecordModel.getDiscount());
			con = JDBCUtil.getConnection();
			ps = con.prepareStatement(sql);
			ps.setString(1, saleRecordModel.getFruitName());
			ps.setInt(2, saleRecordModel.getWeight());
			ps.setBigDecimal(3, saleRecordModel.getInprice());
			ps.setBigDecimal(4, saleRecordModel.getOutPrice());
			ps.setBigDecimal(5, saleRecordModel.getDiscount());
			ps.setString(6, saleRecordModel.getSex());
			ps.setTimestamp(7, saleRecordModel.getDate());
			ps.executeUpdate();

		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			JDBCUtil.close(con, st, ps);
		}
	}

	/**
	 * 查询所有的销售记录
	 * 
	 * @throws SQLException
	 */
	public void selectAll() throws SQLException {
		// TODO Auto-generated method stub
		String sql = "select * from record";
		try {
			con = JDBCUtil.getConnection();
			st = con.createStatement();
			ResultSet rs = st.executeQuery(sql);
			while (rs.next()) {
				SaleRecordModel s = new SaleRecordModel(null, 0, null, null, null, null, null);
				s.setFruitName(rs.getString("fruit_name"));
				s.setWeight(rs.getInt("weight"));
				s.setInprice(rs.getBigDecimal("in_price"));
				s.setOutPrice(rs.getBigDecimal("out_price"));
				s.setDiscount(rs.getBigDecimal("discount"));
				s.setSex(rs.getString("sex"));
				s.setDate(rs.getTimestamp("date"));
				System.out.println(s.toString());
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			JDBCUtil.close(con, st, ps);
		}

	}

	/**
	 * 指定日期内的销售利润
	 * 
	 * @param startTime
	 * @param endTime
	 * @return
	 * @throws SQLException
	 */
	public BigDecimal selectProfitByTime(Timestamp startTime, Timestamp endTime) throws SQLException {
		// TODO Auto-generated method stub
		BigDecimal b = null;
		String sql = "select sum(weight*out_price)-sum(weight*in_price) from record where date between ? and ?";
		try {
			con = JDBCUtil.getConnection();
			ps = con.prepareStatement(sql);
			ps.setTimestamp(1, startTime);
			ps.setTimestamp(2, endTime);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				b = rs.getBigDecimal("sum(weight*out_price)-sum(weight*in_price)");
			}
			return b;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			JDBCUtil.close(con, st, ps);

		}

		return b;
	}

	/**
	 * 销售数量前三名
	 * 
	 * @throws SQLException
	 */
	public void showTopThree() throws SQLException {
		// TODO Auto-generated method stub
		String sql = "SELECT fruit_name,sum(weight) from record GROUP BY fruit_name ORDER BY sum(weight) desc LIMIT 3";
		try {
			con = JDBCUtil.getConnection();
			st = con.createStatement();
			ResultSet rs = st.executeQuery(sql);
			while (rs.next()) {
				String name = rs.getString("fruit_name");
				int saleAmount = rs.getInt("sum(weight)");
				System.out.println(" 水果：" + name + "  销量： " + saleAmount + "kg");
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			JDBCUtil.close(con, st, ps);
		}

	}

	/**
	 * 显示摊位详细信息
	 * 
	 * @throws SQLException
	 */
	public void selectInfo() throws SQLException {
		// TODO Auto-generated method stub
		String sql1 = "SELECT" + " fruit.fruit_name," + " fruit.stock," + " fruit.in_price," + " fruit.out_price,"
				+ " SUM( record.weight )累计销售数量," + " SUM( record.out_price * record.weight )累计销售金额" + " FROM" + " fruit"
				+ " INNER JOIN record ON fruit.fruit_name = record.fruit_name"
				+ " INNER JOIN fruit_in ON record.fruit_name = fruit_in.fruit_name" + " GROUP BY" + " fruit.fruit_name,"
				+ " fruit.stock," + " fruit.in_price," + " fruit.out_price";
		String sql2 = "SELECT SUM(amount)累计进货量 ,SUM(amount*in_price)累计进货金额 FROM fruit_in GROUP BY fruit_name";
		try {
			con = JDBCUtil.getConnection();
			st = con.createStatement();
			Statement st2 = con.createStatement();
			ResultSet rs1 = st.executeQuery(sql1);
			ResultSet rs2 = st2.executeQuery(sql2);
			while (rs1.next() && rs2.next()) {
				String name = rs1.getString("fruit.fruit_name");
				String stock = rs1.getString("fruit.stock");
				BigDecimal inPrice = rs1.getBigDecimal("fruit.in_price");
				BigDecimal outPrice = rs1.getBigDecimal("fruit.out_price");
				int sumSaleCount = rs1.getInt("累计销售数量");
				BigDecimal sumSaleMoney = rs1.getBigDecimal("累计销售金额");
				int sumInCount = rs2.getInt("累计进货量");
				BigDecimal sumInMoney = rs2.getBigDecimal("累计进货金额");
				System.out.println(" 水果：" + name + " 库存数：" + stock + " kg" + " 进价：" + inPrice + "￥  售价：" + outPrice
						+ "￥  累计销量：" + sumSaleCount + " kg" + " 累计销售额：" + sumSaleMoney + "￥  累计进货量：" + sumInCount
						+ " kg 累计进货金额：" + sumInMoney + "￥");
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			JDBCUtil.close(con, st, ps);
		}

	}

}
